import pymysql
class MysqlCURD:
    def __init__(self,host,port,username,pwd,database=None) -> None:
        self.MYSQLHOST = host
        self.MYSQLPORT = port
        self.MYSQLUSERNAME = username
        self.MYSQLPWD = pwd
        self.DATABASE = database
        self.CONN = None
        self.ENGINE = None
        
    def Get_Connection(self):
        if self.DATABASE:
            conn = pymysql.connect(
                host=self.MYSQLHOST,
                port=self.MYSQLPORT,
                user=self.MYSQLUSERNAME,  # 在这里输入用户名
                password=self.MYSQLPWD,  # 在这里输入密码
                charset='utf8mb4',
                database=self.DATABASE  # 指定操作的数据库
            )
            self.CONN = conn
        else:
            conn = pymysql.connect(
                host=self.MYSQLHOST,
                port=self.MYSQLPORT,
                user=self.MYSQLUSERNAME,  # 在这里输入用户名
                password=self.MYSQLPWD,  # 在这里输入密码
                charset='utf8mb4',
            )
            self.CONN = conn

    def Show_Databases(self):
        cursor = self.CONN.cursor()
        try:
            cursor.execute("show databases")
            result = cursor.fetchall()
            print(result)
        except Exception as e:
            print(e)
        finally:
            cursor.close()

    def Is_Database_Exist(self,database_name):
        # 创建游标对象
        cursor = self.CONN.cursor()

        # 执行查询数据库的SQL语句
        cursor.execute("SHOW DATABASES")

        # 获取数据库列表
        databases = cursor.fetchall()

        # 检查目标数据库是否存在
        database_exists = any(database_name in db for db in databases)

        return database_exists


    def Create_Database(self,database_name):
        cursor = self.CONN.cursor()  # 创建游标对象
        try:
            if self.Is_Database_Exist(database_name):
                print(f"数据库{database_name}已存在")
            else:
                # 创建数据库
                cursor.execute(f"create database {database_name}")
                print("数据库创建成功")

        except Exception as e:
            print(e)
            self.CONN.rollback()  # 回滚事务

        finally:
            cursor.close()
  


    
    # 这里设计的是有些缺陷的：如果没有预先指定数据库，此函数将无法运行
    def Is_Table_Exist(self,table_name):
        cursor = self.CONN.cursor()
        # 执行查询表的SQL语句
        # 执行查询数据库的SQL语句
        cursor.execute("show tables")

        # 获取数据库列表
        tables = cursor.fetchall()

        # 检查目标数据库是否存在
        table_exists = any(table_name in db for db in tables)

        return table_exists
    
    def Show_Tables(self):
        cursor = self.CONN.cursor()
        try:
            
            cursor.execute("show tables")
            print(cursor.fetchall())
        except Exception as e:
            print(e)
        finally:
            cursor.close()
            
    
    
    def Create_Table(self,sql,table_name):
        cursor = self.CONN.cursor()  # 创建游标对象
        try:
            if self.Is_Table_Exist(table_name):
                print(f"表{table_name}已存在")
            else:
                cursor.execute(sql)  # 创建表
                print("建表成功")
        except Exception as e:
            print(e)
            self.CONN.rollback()  # 回滚事务

        finally:
            cursor.close()
            # conn.close()  # 关闭数据库连接
            
    def Drop_Table(self,sql):
        cursor = self.CONN.cursor()  # 创建游标对象

        try:
            cursor.execute(sql)  # 创建表

            cursor.execute('show tables')
            print('显示创建的表:', cursor.fetchall())  # 显示创建的表

        except Exception as e:
            print(e)
            self.CONN.rollback()  # 回滚事务

        finally:
            pass
            # cursor.close()
            # conn.close()  # 关闭数据库连接
            
    def Excute_SqlFile(self,path):
        cursor = self.CONN.cursor()
        try:
            # 读取 SQL 文件
            with open(path, 'r',encoding='utf-8') as file:
                sql_statements = file.read()

                # 执行 SQL 语句
                for statement in sql_statements.split(';'):
                    if statement.strip():
                        cursor.execute(statement)

        except Exception as e:
            print(e)
            self.CONN.rollback()  # 回滚事务
        finally:
            # 关闭游标和连接
            cursor.close()

        
    def Close(self):
        self.CONN.close()
    
    
    


    
    def Create_Engine(self,pool_size=5,max_overflow=5):
        # 在上述示例中，`pool_size`指定连接池中的连接数，
        # `max_overflow`指定允许的最大超出连接数。
        # 数据库配置信息
        dic = {
            'host': self.MYSQLHOST,
            'port': self.MYSQLPORT,
            'user': self.MYSQLUSERNAME,  # 在这里输入用户名
            'password': self.MYSQLPWD,  # 在这里输入密码
            'charset': 'utf8mb4',
            'database': self.DATABASE  # 指定操作的数据库
        }

        engine = create_engine("mysql+pymysql://{user}:{password}@{host}:{port}/{database}".format(**dic), 
                               pool_size=pool_size,
                               max_overflow=max_overflow)
        self.ENGINE = engine
        return engine

    
    
    # def InsertData(self,data,Table):
    #     """_summary_

    #     Args:
    #         data (_type_): [(a,b,c,...),(a,b,c,...)]
    #         Table (_type_): _description_

    #     Raises:
    #         e: _description_
    #     """
    #     engine = self.ENGINE
    #     Session = sessionmaker(bind=engine)
    #     session = Session()

    #     try:
    #         # 批量插入数据
    #         records = [Table(*item) for item in data]
    #         session.add_all(records)
    #         session.commit()
    #         print("上传成功")
    #     except Exception as e:
    #         session.rollback()
    #         raise e
    #     finally:
    #         session.close()
    
    def csvToMysql(self,data,table_name):
        """
        :param data: 需要上传的数据
        :param table_name: 上传到的表
        :return:
        """

        # 数据库配置信息
        dic = {
            'host': self.MYSQLHOST,
            'port': self.MYSQLPORT,
            'user': self.MYSQLUSERNAME,  # 在这里输入用户名
            'password': self.MYSQLPWD,  # 在这里输入密码
            'charset': 'utf8mb4',
            'database': self.DATABASE  # 指定操作的数据库
        }

        engine = create_engine("mysql+pymysql://{user}:{password}@{host}:{port}/{database}".format(**dic), max_overflow=5)
        data.to_sql(table_name, engine, index=False, if_exists='replace')
        print('导入成功...')
